集計クエリをTiKVで処理した場合とTiFlashで処理した場合の違いを確認してみた
2024.01.31CX事業本部@大阪の岩田です。最近TiDBの勉強を始めたのでこれから色々とブログにしていこうと思います。今回はTiDBの特徴の1つでもあるストレージエンジンについて簡単な検証を行いました。
TiDBのストレージエンジン
TiDBのストレージエンジンはOLTPワークロードを処理するためのTiKV、OLAPワークロードを処理するためのTiFlashという2つのコンポーネントで構成されています。以下の画像を見てもらうとイメージが湧きやすいと思います。
※画像はTiDB Storage | PingCAP Docsより引用
TiKVは分散型のKVSで内部的にはRocksDBを利用しています。TiKVは行ベースのストレージエンジンで、TiDBにINSERTされたデータはまずTiKV上に格納されます。TiFlashはオプションで利用可能なストレージエンジンで、こちらは列指向のストレージエンジンです。TiKVに格納されたデータはリアルタイムでTiFlashに複製されます。
TiDBのオプティマイザは実行するSQLのコスト見積もりに応じて自動的にTiKVからデータを読み取るべきかTiFlashからデータを読み取るかべきかを判断して実行計画を立てます。この特性がTiDBをHTAPデータベースたらしめている所以です。
やってみる
実際にTiDBにSQLを発行してTiKVとTiFlashそれぞれのストレージエンジンが利用されることを確認してみます。
以後の検証はTiDB Serverlessの6.6を利用しています
MySQL [blog]> select version(); +-------------------------------+ | version() | +-------------------------------+ | 5.7.28-TiDB-v6.6.0-serverless | +-------------------------------+
テストデータの準備
まずテストデータを準備します。以下のブログを参考に、約830万件のデータを作成しました。
まずはテーブルの作成
CREATE TABLE items ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(16), description VARCHAR(30), price INT UNSIGNED, delete_flag tinyint(4) NOT NULL DEFAULT '0', created_at timestamp, updated_at timestamp );
作成したテーブルに1件,2件,4件...とデータをINSERTしていきます
INSERT INTO items () VALUES (); INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items; INSERT INTO items (id) SELECT 0 FROM items;
データが投入されたことを確認します
select count(*) from items; +----------+ | count(*) | +----------+ | 8388608 | +----------+
TiFlashレプリカの作成
データが投入できたのでTiFlashレプリカを作成します。
ALTER TABLE items SET TIFLASH REPLICA 2;
レプリカ作成後に以下のSQLでレプリケートの状況を確認します
SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'blog' and TABLE_NAME = 'items';
以下のようにAVAILABLEが1になっていればTiFlashが利用可能です。
+--------------+------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +--------------+------------+----------+---------------+-----------------+-----------+----------+ | blog | items | 90 | 2 | | 1 | 1 | +--------------+------------+----------+---------------+-----------------+-----------+----------+
これでitemsというテーブルのデータはTiKV,TiFlash両方に存在する状態となりました。
集計クエリを実行してみる
データとストレージエンジン両方の準備が整ったので、実際にSQLを発行しながら実行計画を見てみます。今回はヒント句としてREAD_FROM_STORAGEを指定することで、利用するストレージエンジンを切り替えるようにしました。
まずはTiKVを利用するパターンです。
EXPLAIN ANALYZE SELECT /*+ READ_FROM_STORAGE(TIKV[items]) */ COUNT(*) FROM items;
実行計画は以下のようになりました
+----------------------------+------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------+------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-----------+------+
| HashAgg_13 | 1.00 | 1 | root | | time:2.27s, loops:2, RU:8512.450486, partial_worker:{wall_time:2.265151271s, concurrency:5, task_num:1, tot_wait:11.324940734s, tot_exec:30.076µs, tot_time:11.324982541s, max:2.264998408s, p95:2.264998408s}, final_worker:{wall_time:2.265167317s, concurrency:5, task_num:1, tot_wait:11.325000353s, tot_exec:30.49µs, tot_time:11.325034213s, max:2.265025828s, p95:2.265025828s} | funcs:count(Column#9)->Column#8 | 9.86 KB | N/A |
| └─TableReader_14 | 1.00 | 4 | root | | time:2.27s, loops:2, cop_task: {num: 4, max: 2.26s, min: 560.3ms, avg: 1.26s, p95: 2.26s, max_proc_keys: 4059141, p95_proc_keys: 4059141, tot_proc: 5.02s, rpc_num: 4, rpc_time: 5.02s, copr_cache_hit_ratio: 0.00, build_task_duration: 11ms, max_distsql_concurrency: 4} | data:HashAgg_6 | 325 Bytes | N/A |
| └─HashAgg_6 | 1.00 | 4 | cop[tikv] | | tikv_task:{proc max:2.17s, min:510ms, avg: 1.18s, p80:2.17s, p95:2.17s, iters:8194, tasks:4}, scan_detail: {total_process_keys: 8388608, total_process_keys_size: 448127554, get_snapshot_time: 945.6µs, rocksdb: {block: {}}} | funcs:count(1)->Column#9 | N/A | N/A |
| └─TableFullScan_12 | 8388608.00 | 8388608 | cop[tikv] | table:items | tikv_task:{proc max:2.16s, min:510ms, avg: 1.18s, p80:2.16s, p95:2.16s, iters:8194, tasks:4} | keep order:false | N/A | N/A |
+----------------------------+------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-----------+------+
4 rows in set (2.29 sec)
Task列のcop[tikv]という出力から、テーブルスキャンがTiKVコプロセッサー内で実行されていることが分かります。
続いてTiFlashを利用するパターンを見てみましょう。ヒント句だけ修正し、先程と同様のクエリを実行してみます。
EXPLAIN ANALYZE SELECT /*+ READ_FROM_STORAGE(TIFLASH[items]) */ COUNT(*) FROM items;
実行計画は以下のようになりました。
+------------------------------+------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+-----------+------+
| HashAgg_17 | 1.00 | 1 | root | | time:45.7ms, loops:2, RU:0.000000, partial_worker:{wall_time:45.703585ms, concurrency:5, task_num:1, tot_wait:228.278509ms, tot_exec:14.921µs, tot_time:228.303872ms, max:45.664142ms, p95:45.664142ms}, final_worker:{wall_time:45.727325ms, concurrency:5, task_num:1, tot_wait:228.41126ms, tot_exec:26.335µs, tot_time:228.440612ms, max:45.694138ms, p95:45.694138ms} | funcs:count(Column#9)->Column#8 | 9.86 KB | N/A |
| └─TableReader_19 | 1.00 | 1 | root | | time:45.6ms, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 1, data:ExchangeSender_18 | 316 Bytes | N/A |
| └─ExchangeSender_18 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{time:34.2ms, loops:1, threads:1} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_9 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{time:34.2ms, loops:1, threads:1} | funcs:count(blog.items.delete_flag)->Column#9 | N/A | N/A |
| └─TableFullScan_16 | 8388608.00 | 8388608 | mpp[tiflash] | table:items | tiflash_task:{time:34.2ms, loops:129, threads:4}, tiflash_scan:{dtfile:{total_scanned_packs:1026, total_skipped_packs:0, total_scanned_rows:8388608, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 10ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0} | keep order:false | N/A | N/A |
+------------------------------+------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+-----------+------+
5 rows in set (0.18 sec)
Task列の出力がmpp[tiflash]となり、TiFlashが利用されていることが分かります。
速度を比較してみる
実行計画が確認できたので、TiKVを使う場合とTiFlashを使う場合それぞれについて簡単に速度を比較してみます
以下のコードで各パターンについて100回SQLを実行し、結果を集計してみます。
import pymysql
import time
db_config = {
'host': '<TiDBのエンドポイント>',
'port': 4000,
'user': '<ユーザー名>',
'password': '<パスワード>',
'database': '<DB名>',
'ssl_verify_cert': True,
'ssl_verify_identity': True,
'ssl_ca': "/etc/pki/tls/certs/ca-bundle.crt"
}
connection = pymysql.connect(**db_config)
cursor = connection.cursor()
for i in range(100):
start_time = time.perf_counter()
cursor.execute('EXPLAIN ANALYZE SELECT /*+ READ_FROM_STORAGE(TIFLASH[items]) */ COUNT(*) FROM items;')
end_time = time.perf_counter()
print(f'"tiflash",{i + 1}, {end_time - start_time}')
for i in range(100):
start_time = time.perf_counter()
cursor.execute('EXPLAIN ANALYZE SELECT /*+ READ_FROM_STORAGE(TIKV[items]) */ COUNT(*) FROM items;')
end_time = time.perf_counter()
print(f'"tikv",{i + 1}, {end_time - start_time}')
cursor.close()
結果は以下のようになりました
| ストレージエンジン | 最小値(秒) | 中央値(秒) | 平均値(秒) | 最大値(秒) | 90%タイル(秒) |
|---|---|---|---|---|---|
| TiKv | 1.61977229 | 2.07034218 | 2.1569997 | 3.49727534 | 2.54946744 |
| TiFlash | 0.02377661 | 0.02643901 | 0.02856774 | 0.17495506 | 0.0308234 |
TiFlashを利用することで集計クエリが高速に実行できていることが分かります。
まとめ
TiDBのストレージエンジンについて簡単に検証してみました。個人的にHTAPデータベースを触るのは初めてだったのですが、とても便利だなと感じました。これからTiDBも頑張っていきます💪
